﻿Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Imports Newtonsoft.Json


Public Class oReport
    Public Function LoadUserDefinedReport(ByVal dataBaseName As String, ByVal reportID As String, ByVal jsonParams As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReport")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then

                jsonParams = jsonParams.Insert(0, "[")
                jsonParams = jsonParams.Insert(jsonParams.Length, "]")
                Dim query As String = String.Empty
                Dim dsQuery = GetReportByReportID(dataBaseName, reportID)

                If dsQuery IsNot Nothing AndAlso dsQuery.Tables.Count > 0 AndAlso dsQuery.Tables(0).Rows.Count > 0 Then
                    query = dsQuery.Tables(0).Rows(0)("ReportQueryString")
                End If

                Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(jsonParams)

                Dim dicParams As New Dictionary(Of String, Object)

                If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
                    For Each col As DataColumn In dt.Columns
                        dicParams.Add("@" & col.ColumnName, dt.Rows(0)(col.ColumnName))
                    Next
                End If
                ds = connect.ObjectGetAll_Query_Dynamic(query, dicParams)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function LoadReportParameterFromQuery(ByVal dataBaseName As String, ByVal reportDetailID As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReport")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then

                Dim query As String = String.Empty
                Dim reportParam As New oReportParameter
                Dim dsQuery = reportParam.GetReportParameterByReportDetailID(dataBaseName, reportDetailID)

                If dsQuery IsNot Nothing AndAlso dsQuery.Tables.Count > 0 AndAlso dsQuery.Tables(0).Rows.Count > 0 Then
                    query = dsQuery.Tables(0).Rows(0)("ParaFromQuery")
                End If

                ds = connect.ObjectGetAll_Query_SAP(query)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetReportThisYearVSLastYear(ByVal dataBaseName As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReport")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                ds = connect.ObjectGetAll_Stored_SAP("sp_sys_rpt_ThisYearVSLastYear")
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetAllReport(ByVal dataBaseName As String, ByVal companyCode As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReport")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT T0.*,T1.CompanyName,T2.ReferenceCode,T2.ReferenceName FROM oReport T0 JOIN oCompany T1 ON T0.CompanyCode  = T1.CompanyCode ")
                query.Append(" LEFT JOIN oReferenceValue T2 ON (T0.ReportType = T2.ReferenceValue AND  T2.ReferenceCode ='ReportType')")
                query.Append(" WHERE T0.CompanyCode  = @Param1")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetAllUserDefinedReport(ByVal dataBaseName As String, ByVal companyCode As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReport")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT T0.*,T1.CompanyName,T2.ReferenceCode,T2.ReferenceName FROM oReport T0 JOIN oCompany T1 ON T0.CompanyCode  = T1.CompanyCode ")
                query.Append(" LEFT JOIN oReferenceValue T2 ON (T0.ReportType = T2.ReferenceValue AND  T2.ReferenceCode ='ReportType')")
                query.Append(" WHERE T0.CompanyCode  = @Param1 AND T0.ReportType = 3 ")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetReportByReportID(ByVal dataBaseName As String, ByVal reportID As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReport")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT T0.*,T1.CompanyName,T2.ReferenceCode,T2.ReferenceName FROM oReport T0 JOIN oCompany T1 ON T0.CompanyCode  = T1.CompanyCode ")
                query.Append(" LEFT JOIN oReferenceValue T2 ON (T0.ReportType = T2.ReferenceValue AND  T2.ReferenceCode ='ReportType')")
                query.Append(" WHERE T0.ReportID  = @Param1 ")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {reportID})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function CreateReport(ByVal dataBaseName As String, ByVal reportID As String, ByVal isUpdate As Boolean, ByVal headerJson As String, ByVal lineJson As String) As String
        Dim connect As New Connection()
        Try
            Dim jss As New JavaScriptSerializer()
            Dim header As oReportHeader = jss.Deserialize(Of oReportHeader)(headerJson)
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 AndAlso header IsNot Nothing Then

                Dim query As New StringBuilder
                If isUpdate = False Then
                    query.Append("INSERT INTO [oReport] ")
                    query.Append("  ([ReportCode]")
                    query.Append(",[ReportName]")
                    query.Append(",[CompanyCode]")
                    query.Append(",[ReportType]")
                    query.Append(",[ReportURL]")
                    query.Append(",[ReportQueryString]")
                    query.Append(",[CreatedUserID])")
                    query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7)")

                    Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {header.ReportCode, header.ReportName, header.CompanyCode, _
                                           header.ReportType, header.ReportURL, header.ReportQueryString, header.CreatedUserID})
                    If (count > 0) Then
                        'Get Max Id
                        Dim ds As DataSet = connect.ObjectGetAll_Query_SAP("SELECT IDENT_CURRENT('oReport')")
                        'Insert DocLine
                        If ds IsNot Nothing And ds.Tables.Count > 0 Then
                            Dim objLine As New oReportParameter
                            Dim newDocEntry As Integer = Integer.Parse(ds.Tables(0).Rows(0)(0).ToString())
                            If (lineJson.Count > 0) Then
                                errMsg = objLine.CreateReportParameter(dataBaseName, newDocEntry, isUpdate, lineJson)
                                If errMsg.Length = 0 Then
                                    Return newDocEntry
                                Else
                                    DeleteReport(dataBaseName, newDocEntry)
                                End If
                            Else
                                Return newDocEntry
                            End If
                        End If
                    End If
                Else
                    query = New StringBuilder
                    query.Append("IF EXISTS (SELECT 1 FROM oReport WHERE [ReportID] = @Param6 )")
                    query.Append(" UPDATE  [oReport] SET ")
                    query.Append(" [ReportCode] = @Param1")
                    query.Append(" ,[ReportName] = @Param2")
                    query.Append(" ,[CompanyCode] = @Param3")
                    query.Append(" ,[ReportType] = @Param4")
                    query.Append(" ,[ReportQueryString] = @Param5")
                    query.Append(" WHERE [ReportID] = @Param6")
                    Dim count = connect.Object_Execute_SAP(query.ToString(), New Object() {header.ReportCode, header.ReportName, header.CompanyCode, _
                                           header.ReportType, header.ReportQueryString, header.ReportID})
                    If count > 0 AndAlso lineJson.Count > 0 Then
                        Dim objLine As New oReportParameter
                        errMsg = objLine.CreateReportParameter(dataBaseName, reportID, isUpdate, lineJson)
                        If errMsg.Length = 0 Then
                            Return reportID
                        End If
                    Else
                        Return reportID
                    End If
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function DeleteReport(ByVal dataBaseName As String, ByVal reportID As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim params = New Object() {reportID}
                Dim query As New StringBuilder
                query.Append("DELETE FROM [oReport]")
                query.Append("  WHERE ReportID =  @Param1")
                query.Append("  DELETE FROM [oReportParameter]")
                query.Append("  WHERE ReportID =  @Param1")
                Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Class oReportHeader
        Property ReportID As Integer
        Property ReportCode As String
        Property ReportName As String
        Property CompanyCode As String
        Property ReportType As String
        Property ReportURL As String
        Property ReportQueryString As String
        Property CreatedDate As DateTime
        Property CreatedUserID As Integer
        Public Sub New()
        End Sub
    End Class
End Class
